package com.control;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Course;


public class CourseControl {
	//插入课程
	public boolean insertCourse(Course course){
		DBConnect dbc = new DBConnect();
		String sql = "insert into course(name, teacher_id, flag) values(?, ?, ?)";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,course.getName());		
			dbc.setInt(2,course.getTeacher_id());
			dbc.setInt(3,0);
		    dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	//根据id删除课程
	public boolean deleteCourse(int id){
		DBConnect dbc = new DBConnect();
		String sql = "Delete from course where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,id);
		    dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	//根据课程名获取课程id
	public int getCourseID(String name){
		DBConnect dbc = new DBConnect();
		String sql = "select id from course where name = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setString(1,name);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				return rs.getInt("id");
			}
			return 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			dbc.close();
		}
	}
	//根据课程id获取课程信息
	public Course getCourse(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from course where id = ?";
		Course course = new Course();
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next()){
				course.setId(rs.getInt("id"));
				course.setTeacher_id(rs.getInt("teacher_id"));
				course.setName(rs.getString("name"));
			}
			return course;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	
	//判断是否已选择该课
	public boolean ok(int student_id, int course_id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from score where student_id = ? and course_id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1,student_id);
			dbc.setInt(2,course_id);
			ResultSet rs =  dbc.executeQuery();
			if(rs.next())
				return true;
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	//显示学生选课信息
	public ArrayList<Course> selectCourse(int student_id) {
		ArrayList<Course> list = new ArrayList<Course>();
		DBConnect dbc = new DBConnect();
		String sql = "select * from course";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs =  dbc.executeQuery();
			while(rs.next()) {
				Course course = new Course();
				int course_id = rs.getInt("id");
				course.setId(course_id);
				course.setName(rs.getString("name"));
				course.setTeacher_id(rs.getInt("teacher_id"));
				course.setType(ok(student_id, course_id));
				list.add(course);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	//根据教师id 找到课程id
	public int getCourseId(int teacher_id)
	{

		DBConnect dbc = new DBConnect();
		String sql = "select id from course where teacher_id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, teacher_id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
				
				return rs.getInt("id");
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally {
			dbc.close();
		}
		return 0;
	}
	//判断课程是否锁定，锁定即成绩已经录入，否则没有录入
	public boolean Lock(int id)
	{

		DBConnect dbc = new DBConnect();
		String sql = "select flag from course where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();
			if(rs.next())
			{
				if(rs.getBoolean("flag"))
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
		return false;
	}
	//更改锁定
	public boolean updateLock(int flag, int id)
	{

		DBConnect dbc = new DBConnect();
		String sql = "update course set flag = ? where id = ?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, flag);
			dbc.setInt(2, id);
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
}

